#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import MySQLdb
from MySQLdb.cursors import DictCursor
from DBUtils.PooledDB import PooledDB


class ConnPools:
    poolList = {}

    @classmethod
    def poolGet(cls, host, port, username, password, dbname, charset):
        key = "%s~%s~%s~%s~%s~%s" % (host, port, username, password, dbname, charset)

        pool = ConnPools.poolList.get(key)
        if not pool:
            pool = PooledDB(
                creator=MySQLdb,
                mincached=1,
                maxcached=20,
                host=host,
                port=port,
                user=username,
                passwd=password,
                db=dbname,
                use_unicode=False,
                charset=charset,
                cursorclass=DictCursor,
            )
            ConnPools.poolList[key] = pool

        return pool


class MyConn(object):
    def __init__(self, host, port, username, password, dbname, charset):
        self.pool = ConnPools.poolGet(host, port, username, password, dbname, charset)
        self.conn = self.pool.connection()
        self.cursor = self.conn.cursor()

    def getAll(self, sql, param=None):
        if param is None:
            count = self.cursor.execute(sql)
        else:
            count = self.cursor.execute(sql, param)

        if count > 0:
            result = self.cursor.fetchall()
        else:
            result = False

        return result

    def getOne(self, sql, param=None):
        if param is None:
            count = self.cursor.execute(sql)
        else:
            count = self.cursor.execute(sql, param)

        if count > 0:
            result = self.cursor.fetchone()
        else:
            result = False

        return result

    def getMany(self, sql, num, param=None):
        if param is None:
            count = self.cursor.execute(sql)
        else:
            count = self.cursor.execute(sql, param)

        if count > 0:
            result = self.cursor.fetchmany(num)
        else:
            result = False

        return result

    def insertOne(self, sql, value):
        self.cursor.execute(sql, value)
        return self.__getInsertId()

    def insertMany(self, sql, values):
        count = self.cursor.executemany(sql, values)
        return count

    def __getInsertId(self):
        self.cursor.execute("SELECT @@IDENTITY AS id")
        result = self.cursor.fetchall()
        return result[0]["id"]

    def __query(self, sql, param=None):
        if param is None:
            count = self.cursor.execute(sql)
        else:
            count = self.cursor.execute(sql, param)

        return count

    def update(self, sql, param=None):
        return self.__query(sql, param)

    def delete(self, sql, param=None):
        return self.__query(sql, param)

    def begin(self):
        self.conn.autocommit(0)

    def end(self, option="commit"):
        if option == "commit":
            self.conn.commit()
        else:
            self.conn.rollback()

    def dispose(self, isEnd=1):
        if isEnd == 1:
            self.end("commit")
        else:
            self.end("rollback")

        self.cursor.close()
        self.conn.close()
